-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:物化视图依赖自定义函数，部分合理报错
--step1: 创建自定义函数;expect:成功
drop function if exists multiply_numbers;
CREATE FUNCTION multiply_numbers(a NUMERIC, b NUMERIC)RETURNS NUMERIC
AS $$BEGIN RETURN a * b;
END;
$$ LANGUAGE plpgsql;
/
--step2: 创建基础表;expect:成功
drop table if exists t_ddl0040;
CREATE TABLE t_ddl0040 ( product_id INT, quantity NUMERIC, price NUMERIC);
--step3: 插入示例数据;expect:成功
INSERT INTO t_ddl0040 (product_id, quantity, price) VALUES (1, 10, 20);
INSERT INTO t_ddl0040 (product_id, quantity, price) VALUES (2, 5, 30);
--step4: 创建全量物化视图;expect:成功
drop materialized view if exists mv1;
CREATE MATERIALIZED VIEW mv1
AS SELECT product_id, quantity, price, multiply_numbers(quantity, price)
AS total_t_ddl0040 FROM t_ddl0040;
--step5: 创建增量物化视图;expect:成功
drop materialized view if exists t_ddl0040_summary;
CREATE incremental MATERIALIZED VIEW t_ddl0040_summary
AS SELECT product_id, quantity, price, multiply_numbers(quantity, price)
AS total_t_ddl0040 FROM t_ddl0040;
--step6: 刷新物化视图;expect:成功
REFRESH MATERIALIZED VIEW mv1;
REFRESH MATERIALIZED VIEW t_ddl0040_summary;
--step7: 查询视图;expect:成功
select * from mv1;
select * from t_ddl0040_summary;
--step8: 删除函数;expect:合理报错（存在增量物化视图时无法删除视图依赖的对象）
drop function multiply_numbers;
--step9: 删除增量物化视图;expect:成功
drop materialized view t_ddl0040_summary;
--step10: 删除函数;expect:成功
drop function multiply_numbers;
--step11: 查询视图状态;expect:无效
select valid from pg_object where object_oid='mv1'::regclass;
--step12: 查询视图;expect:成功
select * from mv1;
--step13: 刷新物化视图;expect:合理报错
refresh materialized view mv1;
--step14: 查询视图定义;expect:有告警
select pg_get_viewdef('mv1');
--step15: 重建函数;expect:成功
CREATE FUNCTION multiply_numbers(a NUMERIC, b NUMERIC)RETURNS NUMERIC
AS $$BEGIN RETURN a * b;
END;
$$ LANGUAGE plpgsql;
/
--step16: 刷新物化视图;expect:成功
refresh materialized view mv1;
--step17: 查询视图;expect:成功
select * from mv1;
--step18: 查询视图状态;expect:成功
select valid from pg_object where object_oid='mv1'::regclass;
--step19: 清理环境;expect:成功
drop function multiply_numbers;
drop table t_ddl0040;
drop materialized view mv1;
